在 SELECT 语句中使用函数

除了列名和运算符之外，表达式还可包括一个或多个函数。本章说明如何在 SELECT 语句中使用函数来执行更复杂的数据库查询和数据处理。

有关下列 SQL 函数以及其它 SQL 函数的语法的信息，请参阅《GBase 8s SQL 指南：语法》中表达式段。

提示

还可以使用您自己创建的函数。有关用户定义函数的信息，请参阅创建和使用 SPL 例程和《GBase 8s 用户定义的例程和数据类型开发者指南》。

在 SELECT 语句中使用函数

可以在选择列表中使用任何基本类型的表达式（列、常量、函数、聚集函数和过程）或它们的组合。

函数表达式使用对查询中的每一行进行求值的函数。所有函数表达式都需要参数。当在列名用作参数的情况下使用这一组表达式时，这些表达式包含时间函数和长度函数。

聚集函数

聚集函数对一组查询返回一个值。聚集函数取用依赖于 SELECT 语句的 WHERE 子句返回的一组行的值。没有 WHERE 子句时，聚集函数取用依赖于 FROM 子句组成的所有行的值。

不能将聚集函数用于包含下列数据类型的表达式中：

TEXT
BYTE
CLOB
BLOB
集合数据类型（LIST 、MULTISET 和 SET）
ROW 类型
不透明数据类型（支持不透明数据类型的用户定义的聚集函数除外）

聚集通常用于总结有关表中的行组的信息。此用法在编写高级 SELECT 语句中讨论。当将聚集函数应用于整个表时，结果将包含总结所有选择的行的一行。

所有的 GBase 8s 数据库服务器都支持下列聚集函数。

AVG 函数

下列查询计算 stock 表中所有行的平均值 unit_price。

图: 查询

SELECT AVG (unit_price) FROM stock;

图: 查询结果

(avg)

$197.14

下列查询只计算 stock 表中 manu_code 为 SHM 的那些行的平均值 unit_price。

图: 查询

SELECT AVG (unit_price) FROM stock WHERE manu_code = 'SHM';

图: 查询结果

(avg)

$204.93

COUNT 函数

下列查询对 stock 表中的总行数进行计数和显示。

图: 查询

SELECT COUNT(*) FROM stock;

图: 查询结果

(count(*))

73

下列查询包含 WHERE 子句来对 stock 表中的特定行（在此示例中，是 manu_code 为 SHM 的那些行）进行计数。

图: 查询

SELECT COUNT (*) FROM stock WHERE manu_code = 'SHM';

图: 查询结果

(count(*))

17

通过包含 DISTINCT 关键字（或它的同义词 UNIQUE）和列名，可以计算 stock 表中不同制造商代码的数目。

图: 查询

SELECT COUNT (DISTINCT manu_code) FROM stock;

图: 查询结果

(count)

9

MAX 和 MIN 函数

可以在同一 SELECT 语句中组合聚集函数。例如，可以同时在选择列表中包括 MAX 和 MIN 函数。如下所示。

图: 查询

SELECT MAX (ship_charge), MIN (ship_charge) FROM orders;

该查询查找并显示 orders 表中的最大和最小 ship_charge。

图: 查询结果

(max) (min)

$25.20 $5.00

RANGE 函数

RANGE 函数计算所选行的最大值与最小值之差。

只能将 RANGE 函数应用于数字列。下列查询查找 stock 表中商品的价格范围。

图: 查询

SELECT RANGE(unit_price) FROM stock;

图: 查询结果

(range)

955.50

对于其它聚集函数，当查询包括 GROUP BY 子句时，RANGE 函数适用于组的行，如下所示。

图: 查询

SELECT RANGE(unit_price) FROM stock
GROUP BY manu_code;

图: 查询结果

STDEV 函数

STDEV 函数计算所选行的标准偏差。它是 VARIANCE 函数的平方根。.

可将 STDEV 函数应用于数字列。下列查找入口的标准偏差：

SELECT STDEV(age) FROM u_pop WHERE age > 21;

对于其它聚集，当查询包括 GROUP BY 子句时，STDEV 函数适用于组的行。如下所示：

SELECT STDEV(age) FROM u_pop
GROUP BY state
HAVING STDEV(age) > 21;

除非指定列中的每个值都是空值，否则会忽略空值。如果每个列值都是空值，那么 STDEV 函数对该列返回空值。有关 STDEV 函数的更多信息，请参阅《GBase 8s SQL 指南：语法》中的表达式段。

SUM 函数

下列查询计算 1998 年 7 月 13 日交付的所有订单的总 ship_weight。

图: 查询

SELECT SUM (ship_weight) FROM orders
WHERE ship_date = '07/13/1998';

图: 查询结果

(sum)

130.5

VARIANCE 函数

VARIANCE 函数返回值样本的方差作为所有选择行的方差的无偏估计。它计算以下值：

(SUM(Xi**2) - (SUM(Xi)**2)/N)/(N-1)

在此示例中，Xi 是列中的每个值，N 是列中值的总数。只能将 VARIANCE 函数应用于数字列。以下查询查找入口的标准偏差：

SELECT VARIANCE(age) FROM u_pop WHERE age > 21;

对于其它聚集，当查询包括 GROUP BY 子句时，VARIANCE 函数适用于组的行。如下所示：

SELECT VARIANCE(age) FROM u_pop
GROUP BY birth
HAVING VARIANCE(age) > 21;

除非指定列中的每个值都是空值，否则会忽略空值。如果每个列值都是空值，那么 VARIANCE 函数对该列返回空值。有关 VARIANCE 函数的更多信息，请参阅《GBase 8s SQL 指南：语法》中的表达式段。

将函数应用于表达式

下列查询显示如何将函数应用于算术表达式并为其结果提供显示标签：

图: 查询

SELECT MAX (res_dtime - call_dtime) maximum,
MIN (res_dtime - call_dtime) minimum,
AVG (res_dtime - call_dtime) average
FROM cust_calls;

该查询查找和显示收到和处理客户来电之间的最长、最短和平均时间（以日、小时和分钟计）并相应地标记派生值。该查询结果显示这些时间量。

图: 查询结果

maximum minimum average

5 20:55 0 00:01 1 02:56

时间函数

可以在查询的 Projection 子句或 WHERE 子句中使用时间函数 DAY 、MONTH 、WEEKDAY 和 YEAR 。这些函数返回与用来调用函数的表达式或参数对应的值。还可以使用 CURRENT 或 SYSDATE 函数返回具有当前日期和时间的值，或者使用 EXTEND 函数调整 DATE 或 DATETIME 值。

DAY 和 CURRENT 函数

下列查询在两个 expression 列中对 call_dtime 和 res_dtime 列返回日期（一个月中的某一天）。

图: 查询

SELECT customer_num, DAY (call_dtime), DAY (res_dtime)
FROM cust_calls;

图: 查询结果

customer_num (expression) (expression)

12 12
7 7
1 2
10 10
31
28 28
21 27

下列查询使用 DAY 和 CURRENT 函数来将列值与当前日期（月中某日）进行比较。它只选择值比当前日期早的那些行。在此示例中，CURRENT 日是 15。

图: 查询

SELECT customer_num, DAY (call_dtime), DAY (res_dtime)
FROM cust_calls
WHERE DAY (call_dtime) < DAY (CURRENT);

图: 查询结果

customer_num (expression) (expression)

12 12
7 7
1 2
10 10

下列查询使用 CURRENT 函数来选择除今天打的电话之外的所有来电。

图: 查询

SELECT customer_num, call_code, call_descr
FROM cust_calls
WHERE call_dtime < CURRENT YEAR TO DAY;

图: 查询结果

customer_num  106
call_code     D
call_descr    Order was received, but two of the cans of ANZ tennis balls
within the case were empty

customer_num  110
call_code     L
call_descr     Order placed one month ago (6/7) not received.
⋮
customer_num  116
call_code     I
call_descr    Second complaint from this customer! Received two cases
right-handed outfielder gloves (1 HRO) instead of one case
lefties.

SYSDATE 函数与 CURRENT 函数及其类似，但当未指定 DATETIME 限定符时，其返回值的缺省精度是 DATETIME YEAR TO FRACTION(5)，而不是 CURRENT 的缺省精度 DATETIME YEAR TO FRACTION(3)。

MONTH 函数

下列查询使用 MONTH 函数来抽取和显示在哪个月份接收和处理客户来电，并且它将对结果列使用显式标签。但是，它不区分年份。

图: 查询

SELECT customer_num,
MONTH (call_dtime) call_month,
MONTH (res_dtime) res_month
FROM cust_calls;

图: 查询结果

customer_num   call_month    res_month

          6            6
          7            7
          7            7
          7            7
          7
         11           11
         12           12

如果 DAY 比当前日期早，那么下列查询使用 MONTH 函数和 DAY 及 CURRENT 来显示在哪个月份接收和处理客户来电。

图: 查询

SELECT customer_num,
MONTH (call_dtime) called,
MONTH (res_dtime) resolved
FROM cust_calls
WHERE DAY (res_dtime) < DAY (CURRENT);

图: 查询结果

customer_num     called   resolved

        6          6
        7          7
        7          7

WEEKDAY 函数

下列查询使用 WEEKDAY 函数来指示在星期几接收并处理来电（0 表示星期日，1 表示星期一，以此类推），并标记表达式列。

图: 查询

SELECT customer_num,
WEEKDAY (call_dtime) called,
WEEKDAY (res_dtime) resolved
FROM cust_calls
ORDER BY resolved;

图: 查询结果

customer_num     called   resolved

        3
        0          0
        1          2
        3          3
        3          3
        3          3
        5          4

下列查询使用 COUNT 和 WEEKDAY 函数来对在周末收到的来电进行计数。此类语句能够使您了解客户来电模式或指示是否需要加班费。

图: 查询

SELECT COUNT(*)
FROM cust_calls
WHERE WEEKDAY (call_dtime) IN (0,6);

图: 查询结果

(count(*))

4

YEAR 函数

下列查询检索 call_dtime 比当前年份的开始早的行。

图: 程序

SELECT customer_num, call_code,
YEAR (call_dtime) call_year,
YEAR (res_dtime) res_year
FROM cust_calls
WHERE YEAR (call_dtime) < YEAR (TODAY);

图: 查询结果

customer_num call_code call_year res_year

116 I              1997     1997
116 I              1997     1997

格式化 DATETIME 值

在下列查询中，EXTEND 函数仅显示指定的子字段以限制两个 DATETIME 值。

图: 程序

SELECT customer_num,
EXTEND (call_dtime, month to minute) call_time,
EXTEND (res_dtime, month to minute) res_time
FROM cust_calls
ORDER BY res_time;

该查询为标签为 call_time 和 res_time 的列返回月份至分钟范围，提供工作量的指示。

图: 查询结果

customer_num call_time   res_time

07-31 14:30
06-12 08:20 06-12 08:25
07-01 15:00 07-02 08:21
07-07 10:24 07-07 10:30
07-10 14:05 07-10 14:06
11-28 13:34 11-28 16:47
12-21 11:24 12-27 08:19

TO_CHAR 函数也可以格式化 DATETIME 值。有关内置函数的信息，请参阅 TO_CHAR 函数，该函数也可接受作为参数的 DATE 值或数字值并返回格式化的字符串。

除了这些示例说明的内置时间函数之外，GBase 8s 还支持 ADD_MONTHS 、LAST_DAY 、MDY 、MONTHS_BETWEEN 、NEXT_DAY 和QUARTER 函数。除了这些函数，TRUNC 和 ROUND 函数也可返回更改 DATE 或 DATETIME 参数精度的值。这些附加时间函数在 GBase 8s SQL 指南：语法中进行了描述。

数据转换函数

可以在使用表达式的任何地方使用数据转换函数。

下列转换函数在日期与字符串之间转换：

DATE 函数

DATE 函数将字符串转换为 DATE 值。在以下查询中，DATE 函数将字符串转换为 DATE 值，以允许与 DATETIME 值进行比较。仅当 call_dtime值比指定的 DATE 晚时查询才会检索 DATETIME 值。

图: 查询

SELECT customer_num, call_dtime, res_dtime
FROM cust_calls
WHERE call_dtime > DATE ('12/31/97');

图: 查询结果

customer_num call_dtime       res_dtime

1998-06-12 08:20 1998-06-12 08:25
1998-07-07 10:24 1998-07-07 10:30
1998-07-01 15:00 1998-07-02 08:21
1998-07-10 14:05 1998-07-10 14:06
1998-07-31 14:30

仅当 call_dtime 大于或等于指定日期时，下列查询才会将 DATETIME 值转换为 DATE 格式并带标签显示这些值。

图: 查询

SELECT customer_num,
DATE (call_dtime) called,
DATE (res_dtime) resolved
FROM cust_calls
WHERE call_dtime >= DATE ('1/1/98');

图: 查询结果

customer_num called       resolved

06/12/1998   06/12/1998
07/07/1998   07/07/1998
07/01/1998   07/02/1998
07/10/1998   07/10/1998
07/31/1998

TO_CHAR 函数

TO_CHAR 函数将 DATETIME 或 DATE 值转换为字符串值。TO_CHAR 函数根据您指定的日期格式化伪指令对 DATETIME 值进行求值并返回 NVARCHAR 值。有关受支持的日期格式化伪指令的列表，请参阅《GBase 8s GLS 用户指南》 GL_DATETIME 环境变量的描述。

还可以使用 TO_CHAR 函数将 DATETIME 或 DATE 值转换为 LVARCHAR 值。

下列查询使用 TO_CHAR 函数将 DATETIME 值转换为可读性更强的字符串。

图: 查询

SELECT customer_num,
TO_CHAR(call_dtime,'DY" " FMMONTH" "DD" "YYYY') call_date
FROM cust_calls
WHERE call_code = "B";

图: 查询结果

customer_num  119
call_date     Friday July 01 1998

下列查询使用 TO_CHAR 函数将 DATE 值转换为可读性更强的字符串。

图: 查询

SELECT order_num,
TO_CHAR(ship_date,'DY" " FMMONTH" "DD" "YYYY') date_shipped
FROM orders
WHERE paid_date IS NULL;

图: 查询结果

order_num            1004
date_shipped         Monday May 30 1998

order_num            1006
date_shipped

order_num            1007
date_shipped         Sunday June 05 1998

order_num            1012
date_shipped         Wednesday June 29 1998

order_num            1016
date_shipped         Tuesday July 12 1998

order_num            1017
date_shipped         Wednesday July 13 1998

TO_CHAR 函数还可以格式化数字值。有关内置 TO_CHAR 函数的更多信息，请参阅《GBase 8s SQL 指南：语法》。

TO_DATE 函数

TO_DATE 函数接受字符数据类型的参数并将此值转换为 DATETIME 值。TO_DATE 函数根据您指定的日期格式化伪指令对字符串求值并返回 DATETIME 值。有关受支持的日期格式化伪指令的列表，请参阅《GBase 8s GLS 用户指南》中 GL_DATETIME 环境变量的描述。

还可以使用 TO_DATE 函数将 LVARCHAR 值转换为 DATETIME 值。

下列查询使用 TO_DATE 函数将字符串转换为指定格式的 DATETIME 值。

图: 查询

SELECT customer_num, call_descr
FROM cust_calls
WHERE call_dtime = TO_DATE("2008-07-07 10:24",
"YYYY-MM-DD HH24:MI");

图: 查询结果

customer_num 110

call_descr Order placed one month ago (6/7) not received.

可以使用 DATE 或 TO_DATE 函数来将字符串转换为 DATE 值。TO_DATE 函数的一个优点是它允许您为返回的值指定格式。（可以使用 TO_DATE函数（它总是返回 DATETIME 值）来将字符串转换为 DATE 值，原因是数据库服务器隐式处理 DATE 和 DATETIME 值之间的转换。）

基数函数

CARDINALITY 函数对集合包含的元素数目计数。可以将 CARDINALITY 函数与简单或嵌套集合配合使用。将集合中的任何重复作为个别元素计数。下列查询显示一个查询，它对 manager 表中的每一列返回 department 值和每个 direct_reports 集合中的元素数。

图: 查询

SELECT department, CARDINALITY(direct_reports) FROM manager;

图: 查询结果

department marketing 5

department engineering 7

department publications 4

department accounting 3

还可以从谓词表达式中对集合的元素数进行求值，如下所示。

SELECT department, CARDINALITY(direct_reports) FROM manager
WHERE CARDINALITY(direct_reports) < 6
GROUP BY department;

图: 查询结果

department accounting 3

department marketing 5

department publications 4

智能大对象函数

数据库服务器提供了四个 SQL 函数，您可以从 SQL 语句中调用这些函数来导入和导出智能大对象。下表显示智能大对象函数。

表 1. 智能大对象的 SQL 函数

函数名称	用途
FILETOBLOB()	将文件复制到 BLOB 列中
FILETOCLOB()	将文件复制到 CLOB 列中
LOCOPY()	将 BLOB 或 CLOB 数据复制的另一个 BLOB 或 CLOB 列中
LOTOFILE()	将 BLOB 或 CLOB 数据复制到文件中

有关智能大对象函数的详细信息和语法，请参阅《GBase 8s SQL 指南：语法》中的表达式段。

可以在 SELECT UPDATE 和 INSERT 语句中使用该表显示的任何函数。有关如何在 INSERT 和 UPDATE 语句中使用上述函数的示例，请参阅修改数据。

假设您创建 inmate 和 fbi_list 表，如下图所示。

图: 创建 inmate 和 fbi_list 表

CREATE TABLE inmate
(
id_num   INT,
picture  BLOB,
felony   CLOB
);

CREATE TABLE fbi_list
(
id       INTEGER,
mugshot  BLOB
) PUT mugshot IN (sbspace1);

以下 SELECT 语句使用 LOTOFILE() 函数将数据从 felony 列复制到位于客户机上的 felon_322.txt 文件中：

SELECT id_num, LOTOFILE(felony, 'felon_322.txt', 'client')
FROM inmate
WHERE id_num = 322;

LOTOFILE() 的第一个参数指定将从中导出数据的列的名称。第二个参数指定要将数据复制到其中的文件的名称。第三个参数指定目标文件是位于客户端计算机（'client'）或服务器计算机（'server'）上。

根据源文件是驻留在客户机还是服务器计算机上，下列规则可用来指定函数参数中文件名的路径：

如果源文件驻留在服务器计算机上，那么必须指定文件的全路径名（不是与当前工作目录相对的路径名）。

如果源文件驻留在客户端计算机上，那么可以指定文件的全路径名或相对路径名。

字符串处理函数

字符串处理函数接受类型为 CHAR 、NCHAR 、VARCHAR 、NVARCHAR 或 LVARCHAR 的参数。可以在使用表达式的任何地方使用字符串处理函数。

下列函数在字符串中进行大写字母和小写字母之间的转换：

LOWER
UPPER
INITCAP

下列函数以各种方法处理字符串：

REPLACE
SUBSTR
SUBSTRING
LPAD
RPAD

限制

不能过载任何字符串处理函数来处理扩展数据类型。

LOWER 函数

使用 LOWER 函数来将字符串中的每个大写字母替换为小写字母。LOWER 函数接受字符数据类型的参数并返回具有与指定的参数相同数据类型的值。

下列函数使用 LOWER 函数来将字符串的任何大写字母转换为小写字母。

图: 查询

SELECT manu_code, LOWER(manu_code)
FROM items
WHERE order_num = 1018

图: 查询结果

manu_code   (expression)

PRC          prc
KAR          kar
PRC          prc
SMT          smt
HRO          hro

UPPER 函数

使用 UPPER 函数来将字符串中的每个小写字母替换为大写字母。UPPER 函数接受字符数据类型的参数并返回具有与指定的参数相同数据类型的值。

下列查询中 UPPER 函数将字符串中的任何小写字母转换为大写字母。

图: 查询

SELECT call_code, UPPER(code_descr) FROM call_type

图: 查询结果

call_code    (expression)

B            BILLING ERROR
D            DAMAGED GOODS
I            INCORRECT MERCHANDISE SENT
L            LATE SHIPMENT
O            OTHER

INITCAP 函数

使用 INITCAP 函数来将字符串中每个词的首字母替换为大写字母。每当函数遇到字母之前是非字母字符时，INITCAP 函数就会假设是一个新词。INITCAP 函数接受字符数据类型的参数并返回指定参数相同数据类型的值。

下列查询使用 INITCAP 函数将字符串中每个词的首字母替换为大写字母。

图: 查询

SELECT INITCAP(description) FROM stock
WHERE manu_code = "ANZ";

图: 查询结果

(expression)

Tennis Racquet
Tennis Ball
Volleyball
Volleyball Net
Helmet
Golf Shoes
3 Golf Balls
Running Shoes
Watch
Kick Board
Swim Cap

REPLACE 函数

使用 REPLACE 函数来将字符串中的某一组字符替换为其它字符。

在以下查询中，REPLACE 函数将单元列值 each 替换为查询返回的每一行的 item。

REPLACE 函数的第一个参数是要进行求值的表达式。第二个参数指定想要替换的字符。第三个参数指定要替换除去的字符的新字符串。

图: 查询

SELECT stock_num, REPLACE(unit,"each", "item") cost_per, unit_price
FROM stock
WHERE manu_code = "HRO";

图: 查询结果

stock_num      cost_per   unit_price

            case          $250.00
            case          $126.00
            case          $480.00
            case          $600.00
          case          $260.00
          case          $312.00
          item           $42.50
          item            $4.50
          box           $280.00
          case           $48.00
          case           $40.00
          box            $72.00

SUBSTRING 和 SUBSTR 函数

可以使用 SUBSTRING 和 SUBSTR 函数返回部分字符串。指定开始位置和长度（可选）来确定函数返回字符串的哪部分。

限制

这两个函数在参数中的使用测量单位是字节，而非逻辑字符。这在缺省语言环境和另一个单字节语言环境中都不重要，但是您不能在代码集与其存储长度不同的逻辑字符的语言环境中调用 SUBSTRING 或 SUBSTR。

SUBSTRING 函数

可以使用 SUBSTRING 函数来返回字符串的某部分。指定开始位置和长度（可选）来确定返回字符串的哪部分。可以指定正数或负数作为开始位置。开始位置 1 指定 SUBSTRING 函数从字符串的第一个位置开始。当开始位置为（0）或负数时，SUBSTRING 函数从字符串的开头开始向后计数。

下列查询显示 SUBSTRING 函数的一个示例，其返还查询返回的任何 sname 列值的前四个字符。在本示例中，SUBSTRING 函数从字符串的开头开始，返回开始位置开始的四个字符。

图: 查询

SELECT sname, SUBSTRING(sname FROM 1 FOR 4) FROM state
WHERE code = "AZ";

图: 查询结果

sname (expression)

Arizona Ariz

在下列查询中，SUBSTRING 函数指定开始位置 6，但未指定长度。函数返回从字符串的第六个位置开始到字符串结尾的字符串。

图: 查询

SELECT sname, SUBSTRING(sname FROM 6) FROM state
WHERE code = "WV";

图: 查询结果

sname (expression)

West Virginia Virginia

在下列查询中，SUBSTRING 函数只返回查询返回的任何 sname 列值的第一个字符。对于 SUBSTRING 函数，开始位置 -2 表示从字符的开始位置向后数三个位置（0 、-1 、-2）（对于开始位置 0，函数从字符串的开始位置向后数一个位置）。

图: 查询

SELECT sname, SUBSTRING(sname FROM -2 FOR 4) FROM state
WHERE code = "AZ";

图: 查询结果

sname (expression)

Arizona A

SUBSTR 函数

SUBSTR 函数的作用于 SUBSTRING 函数相同，但两个函数的语法有区别。

要返回字符串的一部分，指定开始位置和长度（可选）来确定 SUBSTR 函数返回子串的哪个部分。为 SUBSTR 函数指定的开始位置可以是正数，也可以是负数。然而，SUBSTR 函数用与 SUBSTRING 函数不同的方式处理开始位置中的负数。当开始位置是负数时，SUBSTR 函数从字符串的末尾开始向后计数，这取决于字符串的长度，而不是字符串包含词或可视字符的长度。SUBSTR 函数将开始位置中的零（0）或 1 识别为字符串中的第一个位置。

下列查询显示包括负数作为开始位置的 SUBSTR 函数的一个示例。假定开始位置为 -15，那么 SUBSTR 函数从字符串末尾开始向后数 15 个位置来找到开始位置，然后返回下五个字符。

图: 查询

SELECT sname, SUBSTR(sname, -15, 5) FROM state
WHERE code = "CA";

图: 查询结果

sname (expression)

California Calif

要使用负数作为开始位置，需要指定求出的长度值。sname 列被定义为 CHAR(15)，因此接受类型为 sname 的参数的 SUBSTR 函数可以将开始位置 0 、1 或 -15 用于函数来返回从字符串的第一个位置开始的字符串。

下列查询返回与图 1

图: 查询

SELECT sname, SUBSTR(sname, 1, 5) FROM state
WHERE code = "CA";

LPAD 函数

使用 LPAD 函数返回已用重复次数达到必要次数的字符序列在左边填充或截断的字符串的副本，这取决于字符串中填充部分的指定长度。指定源字符串、要返回的字符串的长度和要用来填充的字符串。

源字符串和用来填充的字符串的数据类型可以是能转换为 VARCHAR 或 NVARCHAR 的任何数据类型。

下列查询显示具有指定长度 21 个字节的 LPAD 函数的一个示例。由于源字符串长度为 15 个字节（sname 被定义为 CHAR(15)），所以 LPAD 函数填充字符串左边的前六个位置。

图: 查询

SELECT sname, LPAD(sname, 21, "-")
FROM state
WHERE code = "CA" OR code = "AZ";

图: 查询结果

sname (expression)

California ------California
Arizona ------Arizona

RPAD 函数

所以 RPAD 函数返回已用重复次数达到必要次数的字符序列在右边填充或截断的字符串的副本，这取决于字符串中填充部分的指定长度。指定源字符串、要返回的字符串的长度和要用来填充的字符串。

源字符串和用来填充的字符串的数据类型可以是能转换为 VARCHAR 或 NVARCHAR 的任何数据类型。

下列查询显示具有指定长度 21 个字节的 RPAD 函数的一个示例。由于源字符串长度为 15 个字节（sname 被定义为 CHAR(15)），所以 LPAD 函数填充字符串右边的前六个位置。

图: 查询

SELECT sname, RPAD(sname, 21, "-")
FROM state
WHERE code = "WV" OR code = "AZ";

图: 查询结果

sname (expression)

West Virginia West Virginia ------
Arizona Arizona ------

除了这些函数之外，LTRIM 和 RTRIM 函数可以返回删除其字符串参数中指定前导或尾随填充字符的值，并且 ASCII 函数可以返回在其字符串参数中第一个字符的 ASCII 字符集中代码点的数字值。这些内置函数对字符串值的操作在 GBase 8s SQL 指南：语法中进行了描述。

其它函数

还可以在使用常量的 SQL 表达式中的任意位置使用 LENGTH 、USER 、CURRENT 、SYSDATE 和 TODAY 函数。另外，可以在 SELECT 语句中包括 DBSERVERNAME 函数来显示当前数据库所驻留的数据库服务器的名称。

还可以使用这些函数来选择全部由常量值组成的表达式或包括列数据的表达式。在一个实例中，对于所有输出行，结果相同。

另外，可以使用 HEX 函数返回表达式的十六进制编码，使用 ROUND 函数来返回表达式的四舍五入值，使用 TRUNC 函数来返回表达式的截断值。有关上述函数的更多信息，请参阅《GBase 8s SQL 指南：语法》。

LENGTH 函数

在下列查询中，LENGTH 函数针对 company 的长度大于 15 的每个行计算组合 fname 和 lname 列的字节数。

图: 查询

SELECT customer_num,
LENGTH (fname) + LENGTH (lname) namelength
FROM customer
WHERE LENGTH (company) > 15;

图: 查询结果

customer_num    namelength

          11
          13
          11
          14
          11
          10
          10
          10
          12
          11
          10
          12
          10
          11

尽管 LENGTH 函数在使用 DB-Access 时可能不是非常有用，但用于确定程序和报告的长度时它就非常重要。LENGTH 函数返回 CHARACTER 或 VARCHAR 字符串的剪切长度以及 TEXT 或 BYTE 字符串中的全部字节数。

GBase 8s 还支持 CHAR_LENGTH 函数，该函数在其字符串参数中返回逻辑字符数而不是返回字节数。该函数在单个逻辑字符可能需要多个单字节存储的语言环境中非常有用。有关 CHAR_LENGTH 函数的更多信息，请参阅《GBase 8s SQL 指南：语法》和《GBase 8s GLS 用户指南》。

USER 函数

当想要定义仅包含包括您的用户标识行的表的受限视图时，使用 USER 函数。有关如何创建视图的信息，请参阅《 GBase 8s SQL 指南：语法》中的 GRANT 和 CREATE VIEW 语句。

下列查询返回执行查询的用户的用户名（登录用户名），对表中的每行重复一次。

图: 查询

SELECT * FROM cust_calls
WHERE user_id = USER;

如果当前用户的用户名是 richc，该查询仅检索 cust_calls 表中 user_id = richc 的行。

图: 查询结果

customer_num  110
call_dtime    1998-07-07 10:24
user_id       richc
call_code     L
call_descr    Order placed one month ago (6/7) not received.
res_dtime     1998-07-07 10:30
res_descr     Checked with shipping (Ed Smith). Order sent yesterday-we
were waiting for goods from ANZ. Next time will call with
delay if necessary

customer_num  119
call_dtime    1998-07-01 15:00
user_id       richc
call_code     B
call_descr    Bill does not reflect credit from previous order
res_dtime     1998-07-02 08:21
res_descr     Spoke with Jane Akant in Finance. She found the error and is
sending new bill to customer

TODAY 函数

TODAY 函数返回当前系统日期。如果下列查询是在当前系统日期为 1998 年 7 月 10 日时发出的，它返回这一行。

图: 查询

SELECT * FROM orders WHERE order_date = TODAY;

图: 查询结果

order_num      1018
order_date     07/10/1998
customer_num   121
ship_instruct  SW corner of Biltmore Mall
backlog        n
po_num         S22942
ship_date      07/13/1998
ship_weight    70.50
ship_charge    $20.00
paid_date      08/06/1998

DBSERVERNAME 和 SITENAME 函数

可以在 SELECT 语句中包含 DBSERVERNAME （或它的同义词 SITENAME）函数来查询数据库服务器的名称。可以查询 DBSERVERNAME 以找到具有行的任何表，包括系统目录表。

在下列查询中，将标签 server 指定给 DBSERVERNAME 表达式并且也从 systables 系统目录表中选择 tabid 列。此表描述数据库表，tabid 就是表标识。

图: 查询

SELECT DBSERVERNAME server, tabid
FROM systables
WHERE tabid <= 4;

图: 查询结果

server           tabid

montague             1
montague             2
montague             3
montague             4

WHERE 子句限制显示的行数。否则，可能会对 systables 表的每一行显示数据库服务器名一次。

HEX 函数

在下列查询中，HEX 函数返回 customer 中两列的十六进制格式，如下所示。

图: 查询

SELECT HEX (customer_num) hexnum, HEX (zipcode) hexzip
FROM customer;

图: 查询结果

hexnum     hexzip

0x00000065 0x00016F86
0x00000066 0x00016FA5
0x00000067 0x0001705F
0x00000068 0x00016F4A
0x00000069 0x00016F46
0x0000006A 0x00016F6F
⋮

DBINFO 函数

可以在 SELECT 与中调用 DBINFO 函数来查询下列任何信息：

与 tblspace 号或表达式对应的 dbspace 的名称
表中插入的最后一个 SERIAL 、SERIAL8 或 BIGSERIAL 值
SELECT 、INSERT 、DELETE 、UPDATE 、MERGE 、EXECUTE FUNCTION 、EXECUTE PROCEDURE 或 EXECUTE ROUTINE 语句处理的行数
当前会话的会话 ID
会话连接的当前的数据库的名称
INSERT 、UPDATE 或 DELETE 语句是否作为应答事务一部分正在执行
数据库服务器在其上运行的主计算机的名称
操作系统的类型和主计算机的名称
全球标准时间（UTC）格式的本地时区和当前日期和时间
对应于指定的整型列或指定的 UTC 时间值的 DATETIME 值（作为自 1970-01-01 00:00:00+00:00 的秒数）
客户机应用程序连接至的数据库服务器的精确版本或指定完整版本字符串的组件

可以在 SQL 语句中和 SPL 例程中的任何地方使用 DBINFO 函数。

下列查询显示可以和如何使用 DBINFO 函数来找出数据库服务器在其上运行的主计算机的名称。

图: 查询

SELECT FIRST 1 DBINFO('dbhostname') FROM systables;

图: 查询结果

(constant)

lyceum

没有 FIRST 1 子句来限制 tabid 中的值，将对 systables 表的每一行重复数据库服务器在其上运行的计算机的主机名。下列查询显示可以如何使用 DBINFO 函数来找出当前数据库服务器的完整版号和类型。

图: 查询

SELECT FIRST 1 DBINFO('version','full') FROM systables;

有关如何使用 DBINFO 函数查找您当前数据库服务器、数据库会话或数据库的信息的更多信息，请参阅《GBase 8s SQL 指南：语法》。

DECODE 函数

可以使用 DECODE 函数来将具有一个值的表达式转换为另一个值。DECODE 函数具有以下格式：

DECODE(test, a, a_value, b, b_value, ..., n, n_value, exp_m )

在通常情况下，当 a 等于 test 时 DECODE 函数返回 a_value，当 b 等于 test 时，返回 b_value，当 n 等于 test 时返回 n_value 。

如果有若干表达式与 test 匹配，那么 DECODE 返回找到的第一个表达式的 n_value。如果没有表达式与 test 匹配，那么 DECODE 返回 exp_m;；如果没有表达式与 test 匹配并且不存在 exp_m，那么 DECODE 返回 NULL。

限制

DECODE 函数不支持类型为 TEXT 或 BYTE 的参数。

假设包括 emp_id 和 evaluation 列的 employee 表存在。此外还假设对 employee 表执行下列查询则返回以下所示的行。

图: 查询

SELECT emp_id, evaluation FROM employee;

图: 查询结果

emp_id          evaluation

012233          great
012344          poor
012677          NULL
012288          good
012555          very good

在某些情况下，您可能想要转换一组值。例如：假设您想要将前一示例中 evaluation 列的描述值转换为相应的数字值。下列查询显示如何使用DECODE 函数来针对 employee 表中的每一行将 evaluation 列中的值转换为数字值。

图: 查询

SELECT emp_id, DECODE(evaluation, "poor", 0, "fair", 25, "good",50, "very good", 75, "great", 100, -1) AS evaluation
FROM employee;

图: 查询结果

emp_id         evaluation

012233          100
012344          0
012677          -1
012288          50
012555          75
⋮

可为 DECODE 函数的参数指定任何数据类型，只要这些参数满足以下需求：

参数 test 、a 、b 、... 、n 都具有相同的数据类型或求值为公共兼容的数据类型。

参数 a_value 、b_value 、... 、n_value 都具有相同的数据类型或求值为公共兼容的数据类型。

NVL 函数

可以使用 NVL 函数将求值为 NULL 的表达式转换为您指定的值。NVL 函数接受两个参数：第一个参数获取要求值的表达式的名称；第二个参数指定当第一个参数求值为 NULL 时函数返回的值。如果第一个参数求值不为 NULL，那么函数将返回第一个参数的值。假设包括 name 和 address列的 student 表存在。同时假设对 student 表执行以下查询。

图: 查询

SELECT name, address FROM student;

图: 查询结果

name               address

John Smith         333 Vista Drive
Lauren Collier     1129 Greenridge Street
Fred Frith         NULL
Susan Jordan       NULL

以下是包括 NVL 函数的一个示例，该函数为表 address 列包含 NULL 值的每一行返回一个新值。

图: 查询

SELECT name, NVL(address, "address is unknown") AS address
FROM student;

图: 查询结果

name               address

John Smith         333 Vista Drive
Lauren Collier     1129 Greenridge Street
Fred Frith         address is unknown
Susan Jordan       address is unknown

可以为 NVL 函数指定任何数据类型，只要这两个参数求值为公共兼容的数据类型。

如果 NVL 函数的两个参数都求值为 NULL，那么函数返回 NULL。

GBase 8s 还支持 NULLIF 函数。该函数类似于 NVL 函数。但语义不同。如果其两个参数相等，NULLIF 返回 NULL，或者两个参数不相等，将返回第一个参数。有关 NULLIF 函数的更多信息，请参阅《GBase 8s SQL 指南：语法》。

SELECT 语句中的 SPL 例程

本章前面的示例显示由列名、运算符和 SQL 函数组成的 SELECT 语句表达式。本章提供了包含 SPL 例程调用的表达式。

SPL 例程包含特定的存储过程语言（SPL）语句和 SQL 语句。有关 SPL 例程的更多信息，请参阅创建和使用 SPL 例程。

GBase 8s 允许用 C 和 Java™ 编写外部例程。有关更多信息，请参阅《GBase 8s 用户定义的例程和数据类型开发者指南》。

当您在投影列表中包含 SPL 例程表达式时，该 SPL 例程必须是返回单个值（一行一列）的例程。例如：仅当 test_func() 返回单个值时，以下语句才有效：

SELECT col_a, test_func(col_b) FROM tab1
WHERE col_c = "Davis";

当您在 SELECT 语句的 Projection 子句中包含 SPL 例程表达式时，该 SPL 例程必须是返回单个值（一行一列）的例程。例如：仅当 test_func() 返回单个值时，数据库服务器返回一个错误消息。returns more than one value, the database server returns an error message.

SPL 例程通过允许您对选择的每行执行子查询来扩展可用函数的范围。

例如，假设您现有客户号、客户的姓和客户已下订单数的列表。下列查询查询了检索此信息的一种方法。customer 表具有 customer_num 和lname 列，但没有每个客户已下订单数的记录。可以编写 get_orders 例程，该例程查询每个 customer_num 的 orders 表并返回相应订单的数目（标记为 n_orders）。

图: 查询

SELECT customer_num, lname, get_orders(customer_num) n_orders
FROM customer;

该结果显示了此 SPL 例程的输出。

图: 查询结果

customer_num    lname    n_orders

  Pauli           1
  Sadler          9
  Currie          9
  Higgins         4
⋮
  Hanlon          1
  Putnum          1
  Henry           0
  Neelie          1
  Satifer         1
  Lessor          0

使用 SPL 例程来封装查询中经常执行的操作。例如：以下查询中的条件包括例程 conv_price，该例程将库存商品的单击转换为不同的货币并添加任何进口关税。

图: 查询

SELECT stock_num, manu_code, description FROM stock
WHERE conv_price(unit_price, ex_rate = 1.50,
tariff = 50.00) < 1000;

数据加密函数

您可以将 SET ENCRYPTION PASSWORD 语句与内置 SQL 加密函数（使用 Advanced Encryption Standard （AES）和 Triple DES （3DES）加密）一起使用来保护您的敏感数据。如果使用加密，只有拥有正确密码的用户才能读取、复制或修改数据。

将SET ENCRYPTION PASSWORD 语句与下列内置加密和解密函数一起使用：

ENCRYPT_AES

ENCRYPT_AES(data-string-expression
[, password-string-expression [, hint-string-expression ]])

ENCRYPT_TDES

ENCRYPT_TDES (data-string-expression
[, password-string-expression [, hint-string-expression ]])

DECRYPT_CHAR

DECRYPT_CHAR(EncryptedData [, PasswordOrPhrase])

DECRYPT_BINARY

DECRYPT_BINARY(EncryptedData [, PasswordOrPhrase])

GETHINT

GETHINT(EncryptedData)

如果您使用了 SET ENCRYPTION PASSWORD 语句来指定缺省密码，那么数据库服务器将该密码应用于同一会话中调用的对加密和解密函数的后续调用中。

使用 ENCRYPT_AES 和 ENCRYPT_TDES 定义加密的数据，使用 DECRYPT_CHAR 和 DECRYPT_BINARY 查询加密的数据。使用 GETHINT 显示密码提示符（如果在服务器上设置了该字符串）。

可以使用这些 SQL 内置函数来实现列级别或单元级别加密。

使用列级别加密，用相同的密码为给定列上的所有值加密。

使用单元级别加密，用不同的密码为列内的数据加密。

提示

如果想从大型表中选择加密数据，请指定未加密的列。在其中选择行，可对包含加密数据的列创建索引或外键约束，但是这样做对资源使用的效率较低，原因是查询优化器不使用此类索引和外键约束。

使用列级别数据加密来保护信用卡数据

下列示例使用列级别加密来保护信用卡数据。

使用列级别加密来保护信用卡数据：

创建表：create table customer (id char(30), creditcard lvarchar(107));

插入加密数据：

a. 设置会话密码：SET ENCRYPTION PASSWORD "credit card number is encrypted";

b. 加密数据。

INSERT INTO customer VALUES
("Alice", encrypt_aes("1234567890123456"));

INSERT INTO customer VALUES
("Bob", encrypt_aes("2345678901234567"));

使用解密函数查询加密数据。

SET ENCRYPTION PASSWORD "credit card number is encrypted";

SELECT id FROM customer WHERE DECRYPT_CHAR(creditcard) = "2345678901234567";

重要

加密数据值比相应的未加密数据占用更多的存储空间。列宽足够存储明文的列可能需要增大宽度才能支持列级别加密或单元级别加密。如果要将加密值插入声明宽度小于加密字符串的列，那么列存储截断后的值，该值无法被解密。

有关加密安全性的更多信息，请参阅《GBase 8s 管理员指南》。

有关内置加密和解密函数语法和存储要求的更多信息，请参阅《GBase 8s SQL 指南：语法》。

总结

本章介绍了在基本 SELECT 语句中用来查询关系数据库和处理返回数据的函数的样本语法和结果。在 SELECT 语句中使用函数显示如何执行以下操作：

在 Projection 子句中使用聚集函数来计算并检索特定数据。
在 SELECT 语句中包括时间函数 DATE 、DAY 、MDY 、MONTH 、WEEKDAY 、YEAR 、CURRENT 和 EXTEND 以及 TODAY 、LENGTH 和 USER 函数。
在 SELECT 子句中使用转换函数来在日期与字符串之间转换。
在 SELECT 子句中使用字符串处理函数来转换大写和小写字母或以各种方法处理字符串。

在 SELECT 语句中使用函数​

聚集函数​

AVG 函数​

COUNT 函数​

MAX 和 MIN 函数​

RANGE 函数​

STDEV 函数​

SUM 函数​

VARIANCE 函数​

将函数应用于表达式​

时间函数​

DAY 和 CURRENT 函数​

MONTH 函数​

WEEKDAY 函数​

YEAR 函数​

格式化 DATETIME 值​

数据转换函数​

DATE 函数​

TO_CHAR 函数​

TO_DATE 函数​

基数函数​

智能大对象函数​

字符串处理函数​

LOWER 函数​

UPPER 函数​

INITCAP 函数​

REPLACE 函数​

SUBSTRING 和 SUBSTR 函数​

SUBSTRING 函数​

SUBSTR 函数​

LPAD 函数​

RPAD 函数​

其它函数​

LENGTH 函数​

USER 函数​

TODAY 函数​

DBSERVERNAME 和 SITENAME 函数​

HEX 函数​

DBINFO 函数​

DECODE 函数​

NVL 函数​

SELECT 语句中的 SPL 例程​

数据加密函数​

使用列级别数据加密来保护信用卡数据​

总结​

在 SELECT 语句中使用函数

聚集函数

AVG 函数

COUNT 函数

MAX 和 MIN 函数

RANGE 函数

STDEV 函数

SUM 函数

VARIANCE 函数

将函数应用于表达式

时间函数

DAY 和 CURRENT 函数

MONTH 函数

WEEKDAY 函数

YEAR 函数

格式化 DATETIME 值

数据转换函数

DATE 函数

TO_CHAR 函数

TO_DATE 函数

基数函数

智能大对象函数

字符串处理函数

LOWER 函数

UPPER 函数

INITCAP 函数

REPLACE 函数

SUBSTRING 和 SUBSTR 函数

SUBSTRING 函数

SUBSTR 函数

LPAD 函数

RPAD 函数

其它函数

LENGTH 函数

USER 函数

TODAY 函数

DBSERVERNAME 和 SITENAME 函数

HEX 函数

DBINFO 函数

DECODE 函数

NVL 函数

SELECT 语句中的 SPL 例程

数据加密函数

使用列级别数据加密来保护信用卡数据

总结